package tjs.study.notes.dotest.work;

import cn.hutool.core.util.NumberUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.assertj.core.util.Sets;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created by Nicky.Tang on 2023/7/26 10:01
 *
 * @since 02.12.10
 */
public class ER {
    // 物理模型清单地址
//    static String excelFilePath = "/Users/piangpiang/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/5E3B221269B1CEC5D4A7CA7C4EBD3C1F/Caches/Files/2024-10/1544a6f3b2843c2e31613eea4f1228dd/ITSCADA系统-物理模型清单v8.0-自动化.xlsx";
    static String excelFilePath = "/Users/piangpiang/Documents/shsnc/详设编写所需材料/ITSCADA系统概要设计说明v8.0/ITSCADA系统-物理模型清单v8.0.xlsx";
    // 需要哪些表名
    static Set<String> tableNames = Sets.newLinkedHashSet("告警", "实例", "浮点指标性能数据", "用户表", "工作台自定义配置信息");
//    static Set<String> tableNames = Sets.newLinkedHashSet("aiops_alarm_maintenance","alarm_deal_record","maintenance","maintenance_condition","maintenance_timeperiod","notice_strategy","notice_strategy_receiver","notice_strategy_receiver_user","notice_template","alarms_all","cmdb_instance","cmdb_ci_instance_quote","alarms_all","log_all","metric_text","metric_dbl","metric_uint","cmdb_instance","cmdb_ci_instance_quote","alarms_all","log_all","metric_text","metric_dbl","metric_uint","cmdb_instance","cmdb_ci_instance_quote","alarms_all","log_all","metric_text","metric_dbl","metric_uint","cmdb_attr","cmdb_ci","cmdb_ci_relation","cmdb_drop_down","cmdb_drop_down_value","cmdb_instance","metric","snc_alarm_template","snc_trigger","authorization_role","user_group","user_info","user_login_history","cmdb_instance_author","user_organization");

    public static void main(String[] args) {
        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Map<String, List<TableInfo>> tableMap = readTableInfosFromExcel(workbook);
            Map<String, String> tableNameMap = tableMap.values().stream().flatMap(item -> item.stream()).collect(Collectors.toMap(TableInfo::getTableNameRemark, TableInfo::getTableName, (a, b) -> a));
            for (List<TableInfo> tableInfos : tableMap.values()) {
                String createTableDDL = generateCreateTableDDL(tableInfos, tableNameMap);
                System.out.println(createTableDDL);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Map<String, List<TableInfo>> readTableInfosFromExcel(Workbook workbook) {
        Map<String, List<TableInfo>> tableMap = new HashMap<>();
        Sheet sheet = workbook.getSheetAt(0); // Assuming the first sheet contains the table data
        int i = 0;
        for (Row row : sheet) {
            if (i++ < 1) {
                continue;
            }
            String tableName = row.getCell(5).toString();
            String tableNameRemark = row.getCell(4).toString();
            if (!tableNames.isEmpty() && !tableNames.contains(tableName.toLowerCase()) &&!tableNames.contains(tableName) && !tableNames.contains(tableNameRemark.toLowerCase())&& !tableNames.contains(tableNameRemark)) {
                continue;
            }
            String columnName = row.getCell(8).toString();
            String columnNameRemark = row.getCell(7).toString();
            String dataType = row.getCell(10).toString();
            String length = row.getCell(11).toString();
            length = NumberUtil.isNumber(length) ? new BigDecimal(length).intValue() + "" : length;
            boolean isPrimaryKey = "是".equals(row.getCell(12).toString().toUpperCase());
            boolean isNotNull = "是".equals(row.getCell(14).toString().toUpperCase());

            String foreignKeyTableName = "";
            String foreignKeyColumnName = "";

            TableInfo tableInfo = new TableInfo(tableName, tableNameRemark, columnName, columnNameRemark, dataType, length, isPrimaryKey, isNotNull,
                    foreignKeyTableName, foreignKeyColumnName);

            tableMap.computeIfAbsent(tableName, k -> new ArrayList<>()).add(tableInfo);
        }

        return tableMap;
    }

    private static String removeForeignKeyDDL(List<TableInfo> tableInfos, Map<String, String> tableNameMap) {
        StringBuilder sb = new StringBuilder();
        TableInfo firstTableInfo = tableInfos.get(0);
        for (TableInfo tableInfo : tableInfos) {
            String foreignKeyTableName = tableNameMap.get(tableInfo.getForeignKeyTableName());
            String foreignKeyColumnName = tableInfo.getForeignKeyColumnName();
            if (StringUtils.isNotBlank(foreignKeyTableName) && StringUtils.isNotBlank(foreignKeyColumnName)) {
                sb.append("ALTER TABLE ").append(firstTableInfo.getTableName())
                        .append(" DROP FOREIGN KEY ").append(tableInfo.getColumnName()).append(" ;");
            }
        }
        return sb.toString();
    }

    private static String generateForeignKeyDDL(List<TableInfo> tableInfos, Map<String, String> tableNameMap) {
        StringBuilder sb = new StringBuilder();
        TableInfo firstTableInfo = tableInfos.get(0);
        for (TableInfo tableInfo : tableInfos) {
            String foreignKeyTableName = tableNameMap.get(tableInfo.getForeignKeyTableName());
            String foreignKeyColumnName = tableInfo.getForeignKeyColumnName();
            if (StringUtils.isNotBlank(foreignKeyTableName) && StringUtils.isNotBlank(foreignKeyColumnName)) {
                sb.append("\nALTER TABLE ").append(firstTableInfo.getTableName())
                        .append(" ADD FOREIGN KEY (").append(tableInfo.getColumnName()).append(") ")
                        .append("REFERENCES ").append(foreignKeyTableName).append("(").append(foreignKeyColumnName)
                        .append(");");
            }
        }

        return sb.toString();
    }

    private static String generateCreateTableDDL(List<TableInfo> tableInfos, Map<String, String> tableNameMap) {
        StringBuilder sb = new StringBuilder();
        TableInfo firstTableInfo = tableInfos.get(0);

        //sb.append("DROP TABLE IF EXISTS ").append(firstTableInfo.getTableName()).append(" ;\n");
        sb.append("CREATE TABLE ").append(firstTableInfo.getTableName()).append(" (\n");
        boolean exitCreate = false;
        for (int i = 0; i < tableInfos.size(); i++) {
            TableInfo tableInfo = tableInfos.get(i);
            if (tableInfo.getColumnName().equals("创建人") || tableInfo.getColumnNameRemark().equals("创建人")) {
                exitCreate = true;
            }
            sb.append(tableInfo.getColumnName()).append(" ").append(tableInfo.getDataType());
            if (!tableInfo.getLength().isEmpty()) {
                sb.append("(").append(tableInfo.getLength()).append(")");
            }
            if (tableInfo.isPrimaryKey()) {
                sb.append(" PRIMARY KEY");
            }
            if (tableInfo.isNotNull()) {
                sb.append(" NOT NULL");
            }
            if (StringUtils.isNoneEmpty(tableInfo.getColumnNameRemark())) {
                sb.append(" COMMENT '").append(tableInfo.getColumnNameRemark()).append("'");
            }
            if (i < tableInfos.size() - 1) {
                sb.append(",\n ");
            }
        }
        if (!exitCreate) {
            sb.append(",\n").append(" CREATOR_ID VARCHAR(255) NOT NULL COMMENT '创建人',\n" +
                    " CREATE_TIME BIGINT(11) NOT NULL COMMENT '创建时间'");
        }
        sb.append("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='").append(firstTableInfo.getTableNameRemark()).append("';\n");

        return sb.toString();
    }

    public static class TableInfo {
        private String tableName;
        private String tableNameRemark;
        private String columnName;
        private String columnNameRemark;
        private String dataType;
        private String length;
        private boolean isPrimaryKey;
        private boolean isNotNull;
        private String foreignKeyTableName;
        private String foreignKeyColumnName;

        public TableInfo(String tableName, String tableNameRemark, String columnName, String columnNameRemark, String dataType, String length, boolean isPrimaryKey, boolean isNotNull, String foreignKeyTableName, String foreignKeyColumnName) {
            this.tableName = tableName;
            this.tableNameRemark = tableNameRemark;
            this.columnName = columnName;
            this.columnNameRemark = columnNameRemark;
            this.dataType = dataType;
            this.length = length;
            this.isPrimaryKey = isPrimaryKey;
            this.isNotNull = isNotNull;
            this.foreignKeyTableName = foreignKeyTableName;
            this.foreignKeyColumnName = foreignKeyColumnName;
        }

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getColumnName() {
            return columnName;
        }

        public void setColumnName(String columnName) {
            this.columnName = columnName;
        }

        public String getDataType() {
            return dataType;
        }

        public void setDataType(String dataType) {
            this.dataType = dataType;
        }

        public String getLength() {
            return length;
        }

        public void setLength(String length) {
            this.length = length;
        }

        public boolean isPrimaryKey() {
            return isPrimaryKey;
        }

        public void setPrimaryKey(boolean primaryKey) {
            isPrimaryKey = primaryKey;
        }

        public boolean isNotNull() {
            return isNotNull;
        }

        public void setNotNull(boolean notNull) {
            isNotNull = notNull;
        }

        public String getForeignKeyTableName() {
            return foreignKeyTableName;
        }

        public void setForeignKeyTableName(String foreignKeyTableName) {
            this.foreignKeyTableName = foreignKeyTableName;
        }

        public String getForeignKeyColumnName() {
            return foreignKeyColumnName;
        }

        public void setForeignKeyColumnName(String foreignKeyColumnName) {
            this.foreignKeyColumnName = foreignKeyColumnName;
        }

        public String getTableNameRemark() {
            return tableNameRemark;
        }

        public void setTableNameRemark(String tableNameRemark) {
            this.tableNameRemark = tableNameRemark;
        }

        public String getColumnNameRemark() {
            return columnNameRemark;
        }

        public void setColumnNameRemark(String columnNameRemark) {
            this.columnNameRemark = columnNameRemark;
        }
    }


}
